<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/2/8
 * Time: 20:13
 */

namespace app\bill\controller;

use app\base\controller\Common;
use app\bill\model;
use think\Exception;
use think\facade\Request;
use think\Db;
use Watcher\WatchUpdate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Conditional;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\IOFactory;
use RedisClient\RedisClient;
/**
 * 收支管理
 */
class Trading extends Common
{

    const REDIS_DB = 2;

    /**
     * 查看
     */
    public function index(){

        if(Request::isGet()){
            return $this->fetch();
        }

        if(Request::isPost()){
            $post = input('post.');
            $post['page'] = (isset($post['page'])) ? $post['page'] : 1;
            $post['limit'] = (isset($post['limit'])) ? $post['limit'] : 10;

            $where = '';
            if(isset($post['where']))$where = _parseWhere($post['where']);

            $model = new model\Trading();

            $subwhere =  (session('manager.id') != 1) ? [ ['manager_id','=',session('manager.id')] ] : [];

            $res = $model->where($where)->where($subwhere)->page($post['page'] ,$post['limit'])->order('id desc')->select()->toArray();
            //当前分页没有数据，但总数还是有的
            //fixed 第二页数据删除完了之后 layuitable reload 不会自动跳转到 第一页
            if(!count($res) && $model->where($where)->where($subwhere)->count($model->getPk()) > 0){
                $currpage = (($post['page'] - 1) >= 1) ? $post['page'] - 1 : 1 ;
                $res = $model->where($where)->where($subwhere)->page($currpage ,$post['limit'])->select()->toArray();
            }
            $return['data'] = $res;
            $return['count'] = $model->where($where)->where($subwhere)->count();
            $return['code'] = 0;


            return json($return);

        }


    }
    /**
     * 添加
     */
    public function add(){
        $param = input('param.');
        if(isset($param['business_id']) && intval($param['business_id'])){
            $param['business_id'] = intval($param['business_id']);
        }else{
            $param['business_id'] = '';
        }
        $this->assign('business_id' , $param['business_id']);
        return $this->fetch();
    }
    /**
     * 编辑
     */
    public function edit(){
        $id = intval(input('param.id'));
        if(!$id) $this->error('id 不存在');

        $model = new model\Trading();

        $data = $model->find($id);
        //获取原始数据
        $data = $data->getData();

        $this->assign('vo',$data);
        return $this->fetch();
    }

    /**
     * 保存添加
     */
    public function add_action(){
        $post = input('post.');
        $model = new model\Trading();

        $model->startTrans();
        $flag = $model->isUpdate(false)->save($post);
        if(!$flag){
            $model->rollback();
            $this->error('操作失败');
        }

        $res = Db::name('trading')->find($model->getLastInsID());

        $key = date('Y-m',time()).':'.date('d',time());


        $f1 = RedisClient::init(['db' => self::REDIS_DB ])->set($key.':'.$model->getData('id') ,json_encode($res));

        if(!$f1){
            $model->rollback();
            $this->error('redis 同步失败');
        }

        $model->commit();

        $this->success('操作成功');


    }
    /**
     * 保存编辑
     */
    public function edit_action(){
        $post = input('post.');
        $model = new model\Trading();
        $config = [
            'watchTable' => 'trading',
            'logTable' => 'trading_update_log'
        ];
        $watcher = WatchUpdate::init($config);
        $watcher->getOldData($post['id'])->getNewData($post);

        Db::startTrans();

        $time = $model->where('id','=',$post['id'])->value('add_time');
        $tem = explode(' ',$time);
        $tem = explode('-' ,$tem[0]);
        $key = date('Y-m',time()).':'.$tem[2];


        $flag = $model->isUpdate(true)->save($post);
        if(!$flag) {
            Db::rollback();
            throw new Exception('更新失败');
        }
        //同步到redis
        $key = date('Y-m',time()).':'.date('d',time());
        $update_id = $model->getData('id');
        $res = Db::name('trading')->find($update_id); //获取修改之后的数据
        $f1 = RedisClient::init(['db' => self::REDIS_DB])->set($key.':'.$update_id ,json_encode($res));
        if(!$f1){
            $model->rollback();
            $this->error('redis 同步失败');
        }

        //值的过滤&映射器
        $filtermap = [
            'business_id' => ['business_categroy' => 'bname'],
            'type_id' => ['trading_type' => 'type_name'],
            'account_type_id' => ['account_type' => 'account_name'],
            'channel_id' => ['trading_channel' => 'trading_name'],
        ];

        $flag = $watcher->log($filtermap);
        if(!$flag) {
            Db::rollback();
            throw new Exception('日志写入失败');
        }

        Db::commit();
        $new_data = $model->find($post['id'])->toArray();
        $flag ? $this->success('操作成功','',$new_data):$this->error('操作失败');
    }
    /**
     * 表格编辑
     */
    public function table_edit(){
        $post = input('post.');
        if(!isset($post['id']) || !$post['id'])$this->error('缺少 id');

        $model = new model\Trading();
        $flag = $model->isUpdate(true)->save($post);
        $flag ? $this->success('操作成功'):$this->error('操作失败');

    }
    /**
     * 删除
     */
    public function del(){
        $post = input('post.');
        $model = new model\Trading();
        $model->startTrans();

        $time = $model->where('id','=',$post['id'])->value('add_time');
        $tem = explode(' ',$time);
        $tem = explode('-' ,$tem[0]);

        $key = date('Y-m',time()).':'.$tem[2];
        $f1 = RedisClient::init(['db' => self::REDIS_DB])->del($key.':'.$post['id'] );
        if(!$f1){
            $model->rollback();
            $this->error('redis 同步失败');
        }

        $flag = $model->where('id','=',$post['id'])->delete();
        if(!$flag){
            $model->rollback();
            $this->error('操作失败');
        }

        $model->commit();
        $flag ? $this->success('操作成功'):$this->error('操作失败');
    }

    /**
     * 批量删除
     */
    public function pdel(){
        $post = input('post.');
        $model = new model\Trading();
        if(!isset($post['ids']))$this->error('请选选择');

        $model->startTrans();

        $data = $model->field('id,add_time')->where('id','in',$post['ids'])->select();

        foreach($data as $k => $v){
            $tem = explode(' ',$v['add_time']);
            $tem = explode('-' ,$tem[0]);
            $key = date('Y-m',time()).':'.$tem[2];

            $f1 = RedisClient::init(['db' => self::REDIS_DB])->del($key.':'.$v['id'] );
            if(!$f1){
                $model->rollback();
                $this->error('redis 同步失败');
            }
        }

        $flag = $model->where('id','in',$post['ids'])->delete();
        if(!$flag){
            $model->rollback();
            $this->error('操作失败');
        }

        $model->commit();

        $flag ? $this->success('操作成功'):$this->error('操作失败');
    }

    /**
     * 批量操作
     */
    public function batch(){
        $type = input('param.type');
        $value = input('param.value');
        $post = input('post.');
        if(!isset($post['ids']))$this->error('请选选择');

        $model = new model\Trading();
        $update = [$type => $value];
        $flag = Db::table($model->getTable())->where('id','in',$post['ids'])->update($update);
        $flag ? $this->success('操作成功'):$this->error('操作失败');
    }

    /**
     * 导出excel
     */
    public function export(){
        if(Request::isGet()){
            $arr['business_categroy'] = Db::name('business_categroy')->column('bname','id');

            $arr['trading_type'] = Db::name('trading_type')->column('type_name','id');
            $arr['trading_channel'] = Db::name('trading_channel')->column('trading_name','id');
            $arr['account_type'] = Db::name('account_type')->column('account_name','id');

            cache('extend_info',$arr);

            return $this->fetch();
        }

        if(Request::isPost()){
            $post = input('post.');
            if(empty($post['business_id']))
            if(!strpos($post['timeRange'],'~')) $this->error('时间格式不正确');

            $all_month = ['01','02','03','04','05','06','07','08','09','10','11','12'];
            $redisClient = RedisClient::init(['db' => 2]);

            $arr = explode('~', $post['timeRange']);
            $s = explode('-',$arr[0]);
            $s_year = trim($s[0]) ;
            $s_month = trim($s[1]);
            $e= explode('-',$arr[1]);
            $e_year = trim($e[0]);
            $e_month = trim($e[1]);
            if($s_year == $e_year){
                if($s_month == $e_month){
                    $s_e_keys = [$s_month];
                }else{
                    $s_e_keys = array_slice( $all_month , array_keys($all_month ,$s_month)[0] ,($e_month - $s_month)  + 1);
                }

                $allkeys = [];
                foreach($s_e_keys as $k => $v){

                    $arg = $redisClient->keys($s_year.'-'.$v.':*');
                    asort($arg);
                    sort($arg);
                    array_push($allkeys , $arg);
                }



            }else{
                $s_key_month = array_slice( $all_month , array_keys($all_month ,$s_month)[0] ,count($all_month));
                $allkeys = [];
                foreach($s_key_month as $k => $v){
                    $arg = $redisClient->keys($s_year.'-'.$v.':*');
                    asort($arg);
                    sort($arg);
                    array_push($allkeys , $arg);
                }

                $e_key_month = array_slice( $all_month , 0 ,array_keys($all_month ,$e_month)[0] + 1);

                foreach($e_key_month as $k => $v){
                    $arg = $redisClient->keys($e_year.'-'.$v.':*');
                    asort($arg);
                    sort($arg);
                    array_push($allkeys , $arg);
                }


            }
            $keys = [];
            foreach ($allkeys as $k => $v){
                foreach ($v as $sk => $sv){
                    array_push($keys ,$sv);
                }
            }

            $res = [];
            foreach($keys as $k => $v){

                $data = json_decode($redisClient->get($v) ,true);


                if($data['business_id'] == intval($post['business_id']) && $data['type_id'] == intval($post['type_id']) ){

                    //die();
                    if( session('manager.id') != 1 && $data['manager_id'] == session('manager.id') )
                    {
                        array_push($res ,$data);
                    }
                    elseif(session('manager.id') == 1)
                    {
                        array_push($res ,$data);
                    }
                    else
                    {
                        continue;
                    }
                }else{
                    continue;
                }

            }

            $extend_info = cache('extend_info');

            foreach($res as $k => $v){
                $res[$k]['business_id'] = isset($extend_info['business_categroy'][$v['business_id']]) ? $extend_info['business_categroy'][$v['business_id']] : '暂无';
                $res[$k]['type_id'] = isset($extend_info['trading_type'][$v['type_id']]) ? $extend_info['trading_type'][$v['type_id']] : '暂无';
                $res[$k]['channel_id'] = isset($extend_info['trading_channel'][$v['channel_id']]) ? $extend_info['trading_channel'][$v['channel_id']] :'暂无';
                $res[$k]['account_type_id'] = isset($extend_info['account_type'][$v['account_type_id']]) ? $extend_info['account_type'][$v['account_type_id']]: '';
            }


//            $arr[0] = trim($arr[0]);
//            $arr[1] = trim($arr[1]);
//            $model = new model\Trading();
//            if(session('manager.id') != 1){
//                $res =  $model->where([
//                    ['business_id' ,'=', intval($post['business_id']) ],
//                    [ 'type_id' ,'=', intval($post['type_id'])] ,
//                    ['','EXP',DB::raw('DATE_FORMAT(add_time,\'%Y-%m\') <= "'.$arr[0].'"' )],
//                    ['','EXP',DB::raw('DATE_FORMAT(add_time,\'%Y-%m\') >= "'.$arr[0].'"' )],
//                    ['manager_id','=',session('manager.id')],
//                ])->select();
//            }else{
//                $res =  $model->where([
//                    ['business_id' ,'=', intval($post['business_id']) ],
//                    [ 'type_id' ,'=', intval($post['type_id'])] ,
//                    ['','EXP',DB::raw('DATE_FORMAT(add_time,\'%Y-%m\') <= "'.$arr[0].'"' )],
//                    ['','EXP',DB::raw('DATE_FORMAT(add_time,\'%Y-%m\') >= "'.$arr[0].'"' )],
//                ])->select();
//            }
//
//
//            $res = $res ? $res->toArray() : $this->error('查询出错了');

            $spreadsheet = new Spreadsheet();


            $sheet = $spreadsheet->getActiveSheet();

            $sheet = [
                'A' => ['id','账单id' ,'width' => 10],
                'B' => ['business_id','业务种类','width' => 15],
                'C'=>  ['type_id' ,'收支类型','width' => 15],
                'D' => ['money' ,'金额','width' => 15],
                'E' => ['service_money' ,'手续费','width' => 15],
                'F'=>  ['channel_id','交易渠道','width' => 25],
                'G'=>  ['account_type_id','账号类型','width' => 25],
                'H'=>  ['account_name','账号名称','width' => 25],
                'I' => ['add_time','添加时间','width' => 25],
                'J' => ['update_time','修改时间','width' => 25],

            ];
            $num = 1;
            foreach($sheet as $k => $v){
                //p($k.$num);
                $spreadsheet->getActiveSheet()->setCellValue($k.$num, $v[1]);
                foreach($res as $sk => $sv){
                    //p($k.($sk+2) .'---'.$sv[$v[0]]);
                    $spreadsheet->getActiveSheet()->setCellValue($k.($sk+2) ,$sv[$v[0]]);
                    $spreadsheet->getActiveSheet()->getStyle($k.($sk+2))->getAlignment()->setHorizontal('center');
                }
                $spreadsheet->getActiveSheet()->getStyle($k.$num)->getFont()->setBold(true);
                $spreadsheet->getActiveSheet()->getStyle($k.$num)->getFont()->setBold(true);
                $spreadsheet->getActiveSheet()->getStyle($k.$num)->getAlignment()->setHorizontal('center');
                $spreadsheet->getActiveSheet()->getColumnDimension($k)->setWidth($v['width']);
            }
           // $spreadsheet->getActiveSheet()->setCellValue('B7', '=SUM(B5:C5)');
            $spreadsheet->getActiveSheet()->setTitle('报表');

            //统计
            $spreadsheet->getActiveSheet()->setCellValue('C'.($sk+3),'合计：');
            $spreadsheet->getActiveSheet()->getStyle('C'.($sk+3))->getAlignment()->setHorizontal('center');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($sk+3),'=SUM(D2:D'.($sk+2).')');
            $spreadsheet->getActiveSheet()->getStyle('D'.($sk+3))->getAlignment()->setHorizontal('center');
            $spreadsheet->getActiveSheet()->setCellValue('E'.($sk+3),'=SUM(E2:E'.($sk+2).')');
            $spreadsheet->getActiveSheet()->getStyle('E'.($sk+3))->getAlignment()->setHorizontal('center');

            //die();
            // Redirect output to a client’s web browser (Xls)
            $file_name = date('Y-m-d H:i:s' ,time());
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$file_name.'.xls"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');

            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0

            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            $writer->save('php://output');
//        $writer = new Xlsx($spreadsheet);
//        $writer->save('hello world.xlsx');
        }



    }

    public function sync_redis(){
        $id = input('param.id');
        $id = intval($id) ? $id : 1;

        $res = Db::name('trading')->find(intval($id));

        if(!$res) die('同步完成');

        $data = $res;
        $t = explode(' ',$data['add_time']);
        $tt = explode('-',$t[0]);

        $key = $tt[0] .'-'. $tt[1] .':'.$tt[2] .':'.$data['id'];

        $config['db'] = 2;
        $f1 = RedisClient::init($config)->set($key ,json_encode($data));
        if(!$f1) die('同步第'.$data['id'].'数据失败');

        echo '同步第'.$data['id'].'数据成功';


        $next_url = url('trading/sync_redis',['id' => ($data['id'] + 1)]);
        p($next_url);
        //sleep(0.5);
        echo '<script>window.location.href = "'.$next_url.'"</script>';

    }

}